﻿using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Koonsoft.HYCRM.BLL;

namespace Koonsoft.FixOrderID
{
    public class OrderHelper
    {
        public DataTable GetNullOrderID()
        {
            const string sql = @"select * from [order] where orderid is null order by ordertime";

            var sqlconn = new SqlConnection
                              {
                                  ConnectionString = ConfigurationManager.ConnectionStrings["KOONCRMDB"].ConnectionString
                              };

            var sqlcmd = new SqlCommand
                             {
                                 Connection = sqlconn,
                                 CommandText = sql
                             };

            try
            {
                var ds = new DataSet();

                var sqlda = new SqlDataAdapter(sqlcmd);

                sqlda.Fill(ds);

                return ds.Tables[0];
            }
            finally
            {

            }
        }

        public void ReBuildOrderID(DataTable dt)
        {
            if ((dt != null) && (dt.Rows.Count > 0))
            {
                Console.WriteLine("待处理订单{0}条", dt.Rows.Count);

                foreach (DataRow row in dt.Rows)
                {
                    var orderdate = Convert.ToDateTime(row["OrderTime"]);
                    var clientID = Convert.ToString(row["ClientID"]);
                    var companySerialNumber = Convert.ToString(row["CompanySerialNumber"]);

                    var orderid = string.Format("{0:yyMMdd}", orderdate) + new UtilityBLL().GetRegularDateVal("OrderNo", 4, String.Format("{0:yyyyMMdd}", orderdate));

                    UpdateOrderID(clientID, companySerialNumber, orderid);
                    Console.WriteLine("生成订单ID：{0}", orderid);
                }

                Console.WriteLine("新订单ID生成完毕，共计{0}单", dt.Rows.Count);
            }
        }

        public void UpdateOrderID(string clientID, string companySerialNumber, string orderID)
        {
            const string sql = @"update [order] set orderid=@orderid where companySerialNumber=@companySerialNumber and clientid=@clientID";

            var sqlconn = new SqlConnection
            {
                ConnectionString = ConfigurationManager.ConnectionStrings["KOONCRMDB"].ConnectionString
            };

            var sqlcmd = new SqlCommand
            {
                Connection = sqlconn,
                CommandText = sql
            };

            sqlcmd.Parameters.Add("@orderid", SqlDbType.NVarChar, 10);
            sqlcmd.Parameters["@orderid"].Value = orderID;

            sqlcmd.Parameters.Add("@companySerialNumber", SqlDbType.NVarChar, 36);
            sqlcmd.Parameters["@companySerialNumber"].Value = companySerialNumber;

            sqlcmd.Parameters.Add("@clientID", SqlDbType.NVarChar, 36);
            sqlcmd.Parameters["@clientID"].Value = clientID;

            try
            {
                sqlcmd.Connection.Open();
                sqlcmd.ExecuteNonQuery();
            }
            finally
            {
                sqlcmd.Connection.Close();
            }
        }

        //public string GetRegularDateVal(string seqName, int length, string currDate)
        //{
        //    const string sql = "Utility_GetRegularDateVal";

        //    using (var cmd = database.GetStoredProcCommand(sql))
        //    {
        //        database.AddInParameter(cmd, "seqname", DbType.String, seqName);
        //        database.AddInParameter(cmd, "length", DbType.Int32, length);
        //        database.AddInParameter(cmd, "currDate", DbType.String, currDate);
        //        database.AddOutParameter(cmd, "RegularVal", DbType.String, 50);
        //        database.ExecuteNonQuery(cmd);

        //        return cmd.Parameters["@RegularVal"].Value.ToString();
        //    }
        //}
    }
}
